package md.mru.core.repositories;

import md.mru.core.domains.orders.FolderOrder;
import md.mru.core.domains.type.OrderStateType;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * @author sergiu.marin
 * @since 01.04.2014
 */
public interface OrderRepository extends JpaRepository<FolderOrder, Long> {

    @Query("SELECT o FROM FolderOrder o LEFT JOIN o.folders f WHERE f.id=:folderId")
    Page<FolderOrder> loadFolderOrders(@Param(value = "folderId") long folderId, Pageable request);

    @Query("SELECT o FROM FolderOrder o LEFT JOIN FETCH o.folders f WHERE f.folderState.id IN :folderStateIds AND o.toDate < CURRENT_DATE")
    List<FolderOrder> loadOrderWithExpiredFolderStates(@Param(value = "folderStateIds") Set<Long> folderStateIds);

    @Query(value = "SELECT MAX(o.order_date) FROM orders o " +
                        "INNER JOIN orders_folders f ON o.id=f.order_id AND f.folder_id=:folderId " +
                        "INNER JOIN order_types ot ON o.order_type_id=ot.id " +
                        "INNER JOIN order_type_groups otg ON ot.order_type_group_id=otg.id " +
                    "WHERE otg.id IN :orderTypeGroupIds", nativeQuery = true)
    Date getFolderEmployedDate(@Param(value = "folderId") long folderId, @Param(value = "orderTypeGroupIds") List<Long> orderTypeGroupIds);
}
